package com.xishu.dao.jpa;

import com.xishu.entity.order.OrderWaiterMapping;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface OrderWaiterMappingDao extends JpaRepository<OrderWaiterMapping, Long>, JpaSpecificationExecutor<OrderWaiterMapping> {
    /**
     * 年月日选项，不带名称查
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x  , waiter_name as y, sum(cash) as value " +
            "from order_waiter_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by m.waiter_name,FROM_UNIXTIME(create_time / 1000, :timeUnit)" , nativeQuery = true)
    public List<Tuple> report(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit);

    /**
     * 年月日选项，带中文名称查
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param waiterName_zh
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x  , waiter_name as y, sum(cash) as value " +
            "from order_waiter_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "and m.waiter_name in (:waiterName_zh)"+
            "group by m.waiter_name,FROM_UNIXTIME(create_time / 1000, :timeUnit)" , nativeQuery = true)
    public List<Tuple> idReportZh(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit,List<String> waiterName_zh);

    /**
     * 年月日选项，带英文名称查
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param waiterName_en
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x  , waiter_name as y, sum(cash) as value " +
            "from order_waiter_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "and m.waiter_name in (:waiterName_en)"+
            "group by m.waiter_name,FROM_UNIXTIME(create_time / 1000, :timeUnit)" , nativeQuery = true)
    public List<Tuple> idReportEn(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit,List<String> waiterName_en);


    /**
     * 总计选项，带中文名称查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param waiterName_zh
     * @return
     */
    @Query(value = "select waiter_name as y, sum(cash) as value " +
            "from order_waiter_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "and m.waiter_name in (:waiterName_zh)"+
            "group by m.waiter_name" , nativeQuery = true)
    List<Tuple> idReportTotalZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> waiterName_zh);

    /**
     * 总计选项，带英文名称查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param waiterName_en
     * @return
     */
    @Query(value = "select waiter_name as y, sum(cash) as value " +
            "from order_waiter_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "and m.waiter_name in (:waiterName_en)"+
            "group by m.waiter_name" , nativeQuery = true)
    List<Tuple> idReportTotalEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> waiterName_en);

    /**
     * 总计选项，不带名称查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select waiter_name as y, sum(cash) as value " +
            "from order_waiter_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by m.waiter_id" , nativeQuery = true)
    List<Tuple> reportTotal(List<Long> shopIdList, Long startTime, Long endTime);

    @Query(value = "select waiter_name as name_zh,waiter_name as name_en from order_waiter_mapping m " +
            "where m.shop_id =:shopId group by waiter_name", nativeQuery = true)
    public List<Tuple> shopSearchItem(Long shopId);

}
